package com.infolab.aims.service;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.JoinType;
import javax.persistence.criteria.ParameterExpression;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.infolab.aims.common.CommonUtil;
import com.infolab.aims.common.InQueryHelper;
import com.infolab.aims.common.PaginationHelper;
import com.infolab.aims.common.QueryHelper;
import com.infolab.aims.common.bean.SearchCriteria;
import com.infolab.aims.common.bean.SearchResult;
import com.infolab.aims.domain.BlanceView;
import com.infolab.aims.domain.Location;
import com.infolab.aims.domain.asset.AssetBalance;
import com.infolab.aims.domain.asset.AssetBalanceItem;
import com.infolab.aims.domain.asset.AssetComponent;
import com.infolab.aims.domain.asset.AssetEquipment;
import com.infolab.aims.domain.asset.AssetEquipmentBalance;
import com.infolab.aims.domain.asset.AssetEquipmentView;
import com.infolab.aims.domain.asset.AssetModel;
import com.infolab.aims.domain.asset.AssetTransaction;
import com.infolab.aims.domain.purchase.PurchaseOrder;
import com.infolab.aims.service.intf.AssetModelService;


@Service
@Transactional
public class AssetModelServiceImpl implements AssetModelService {
	
	Log logger = LogFactory.getLog(getClass());
	@PersistenceContext
	private EntityManager em;
	
	public void createAssetModel(AssetModel assetModel)
	{
		assetModel.setActive(true);
		
		AssetModel newAssetModel = new AssetModel();
		
		assetModel.setModifiedBy(assetModel.getCreatedBy());
		assetModel.setDateCreated(new Date());
		assetModel.setDateModified(new Date());
		
		BeanUtils.copyProperties(assetModel,newAssetModel);
		
		em.persist(newAssetModel);
		
		assetModel.setAssetModelId(newAssetModel.getAssetModelId());
		
	}

	public void updateAssetModel(AssetModel assetModel) {
		
//		if(assetModel.getSection()==null || assetModel.getSection().getSectionId()==null)
//			assetModel.setSection(null);
		
		AssetModel newAssetModel = em.find(AssetModel.class, assetModel.getAssetModelId());
		
		assetModel.setDateModified(new Date());
		
		BeanUtils.copyProperties(assetModel,newAssetModel);
		
		em.merge(newAssetModel);
	}
	
	public void updateSerialNo(AssetEquipment assetEquipment) {
		
		Query query = em.createQuery("update AssetEquipment set serialNo=:newSerialNo,modifiedBy=:modifiedBy,datemodified=:dateModified where serialNo=:oldSerialNo");
		query.setParameter("newSerialNo", assetEquipment.getNewSerialNo());
		query.setParameter("oldSerialNo", assetEquipment.getAssetEquipmentPK().getSerialNo());
		query.setParameter("modifiedBy", assetEquipment.getModifiedBy());
		query.setParameter("dateModified", new Date());
		query.executeUpdate();
		
		query = em.createQuery("update WorkOrderPart set oldSerialNo=:newSerialNo where oldSerialNo=:oldSerialNo");
		query.setParameter("newSerialNo", assetEquipment.getNewSerialNo());
		query.setParameter("oldSerialNo", assetEquipment.getAssetEquipmentPK().getSerialNo());
		query.executeUpdate();
		
	}
	
	public void updateToFaulty(AssetEquipment assetEquipment) {
		
		Query query = em.createQuery("update AssetEquipment set assetequipmentstatus=:status,modifiedBy=:modifiedBy,datemodified=:dateModified where serialNo=:serialNumber");
		query.setParameter("status", assetEquipment.getAssetEquipmentStatus());
		query.setParameter("serialNumber", assetEquipment.getAssetEquipmentPK().getSerialNo());
		query.setParameter("modifiedBy", assetEquipment.getModifiedBy());
		query.setParameter("dateModified", new Date());
		query.executeUpdate();				
	}
	
	public boolean checkAvailabilitySerialNo(String newSerialNo) {
		
		SearchCriteria searchCriteria  = new SearchCriteria();
		searchCriteria.addSearchCriteria("serialNo", newSerialNo);
		AssetEquipment assetEquipment = findAssetEquipment(searchCriteria);
		
		if(assetEquipment!=null)
			return false;
			
		return true;	
		
	}
	
	public void updateAssetEquipments(AssetModel assetModel) {
		
		List<AssetEquipment> assetEquipments = assetModel.getAssetEquipments();
		if(assetEquipments!=null)
		{
			for(int i=0;i<assetEquipments.size();i++)
			{
				AssetEquipment assetEquipment = assetEquipments.get(i);
				if(assetEquipment.isDeleted())
				{
					assetEquipment = em.getReference(AssetEquipment.class,assetEquipment.getAssetEquipmentPK());
					em.remove(assetEquipment);
					
				}
				else
					em.merge(assetEquipment);
			}
			
		}
		
	}


	public AssetModel getAssetModel(Integer assetModelId) {
		
		return em.find(AssetModel.class, assetModelId);
	}

	public List<AssetModel> findAssetModels(SearchCriteria searchCriteria) {
		if(searchCriteria==null) searchCriteria = new SearchCriteria();
		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery<AssetModel> cq = cb.createQuery(AssetModel.class);
		Root<AssetModel> root = cq.from(AssetModel.class);
		List<Predicate> criteria = new ArrayList<Predicate>();
		
		if (searchCriteria.getSearchCriteria("assetModelCode")!= null) {
			ParameterExpression<String> p = cb.parameter(String.class, "assetModelCode");
			criteria.add(cb.equal(cb.upper(root.<String>get("assetModelCode")), cb.upper( p)));
		}
		if (searchCriteria.getSearchCriteria("assetType")!= null) {
			ParameterExpression<String> p = cb.parameter(String.class, "assetType");
			criteria.add(cb.equal(root.get("assetType"),  p));
		}
		if (searchCriteria.getSearchCriteria("accessDepartmentId")!= null) {
			ParameterExpression<Integer> p = cb.parameter(Integer.class, "accessDepartmentId");
			criteria.add(cb.or(cb.isNull(root.<String>get("accessDepartmentId")),cb.equal(cb.upper(root.<String>get("accessDepartmentId")), p)));
		}
		
		criteria.add(cb.equal(root.get("active"),  true));
		
		cq.select(root);
		if (criteria.size() == 0) {
			} else if (criteria.size() == 1) {
			cq.where(criteria.get(0));
			} else {
			cq.where(cb.and(criteria.toArray(new Predicate[0])));
		}
		
		if(StringUtils.isEmpty(searchCriteria.getSortName()))
			cq.orderBy(cb.asc(root.get("assetModelName")));
		
		Query query = em.createQuery(cq);

		if (searchCriteria.getSearchCriteria("assetModelCode")!= null) {
			query.setParameter("assetModelCode", searchCriteria.getSearchCriteria("assetModelCode"));
		}
		if (searchCriteria.getSearchCriteria("assetType")!= null) {
			query.setParameter("assetType", searchCriteria.getSearchCriteria("assetType"));
		}
		if (searchCriteria.getSearchCriteria("accessDepartmentId")!= null) {
			query.setParameter("accessDepartmentId", searchCriteria.getSearchCriteria("accessDepartmentId"));
		}
		if(searchCriteria.getMaxRow()!=null)
			query.setMaxResults(searchCriteria.getMaxRow());
		
		return query.getResultList();
	}
	
	public List<AssetEquipmentBalance> findAssetEquipmentBalances(SearchCriteria searchCriteria) {
		Query query = em.createNamedQuery("AssetEquipmentBalance.getBalance");
		query.setParameter("assetModelId", searchCriteria.getSearchCriteria("assetModelId"));
		return query.getResultList();
	}
	
	public List<AssetEquipmentView> findAssetEquipmentsView(SearchCriteria searchCriteria) {
		if(searchCriteria==null) searchCriteria = new SearchCriteria();
		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery<AssetEquipmentView> cq = cb.createQuery(AssetEquipmentView.class);
		Root<AssetEquipmentView> root = cq.from(AssetEquipmentView.class);
		List<Predicate> criteria = new ArrayList<Predicate>();
		if (searchCriteria.getSearchCriteria("locationId")!= null) {
			ParameterExpression<Integer> p = cb.parameter(Integer.class, "locationId");
			criteria.add(cb.equal(root.get("locationId"),  searchCriteria.getSearchCriteria("locationId")));
		}
		cq.select(root);
		if (criteria.size() == 0) {
			} else if (criteria.size() == 1) {
			cq.where(criteria.get(0));
			} else {
			cq.where(cb.and(criteria.toArray(new Predicate[0])));
		}
		
		if(org.apache.commons.lang.StringUtils.isEmpty(searchCriteria.getSortName()))
		{
			cq.orderBy(cb.asc(root.get("serialNo")));
		}

		Query query = em.createQuery(cq);
		
		if(searchCriteria.getMaxRow()!=null)
			query.setMaxResults(searchCriteria.getMaxRow());

		return query.getResultList();
	}
	
	
	public List<String> searchSerialNoCriteria(SearchCriteria searchCriteria) {

		if(searchCriteria==null) searchCriteria = new SearchCriteria();
		CriteriaBuilder cb = em.getCriteriaBuilder();
		//CriteriaQuery<AssetEquipment> cq = cb.createQuery(AssetEquipment.class);
		CriteriaQuery<Object> cq = cb.createQuery(Object.class);
		List<Predicate> criteria = new ArrayList<Predicate>();
		Root<AssetEquipment> root = cq.from(AssetEquipment.class);		
		
		
		
		if (searchCriteria.getSearchCriteria("serialNo")!= null) {
			
			Object obj = searchCriteria.getSearchCriteria("serialNo");
			if(obj instanceof InQueryHelper)
			{
				ParameterExpression<List> p = cb.parameter(List.class, "serialNo");
				criteria.add(cb.in(root.get("assetEquipmentPK").get("serialNo")).value(p));
			}
		}
		
		cq.select(root.get("assetEquipmentPK").get("serialNo"));
		

		if (criteria.size() == 0) {
		} else if (criteria.size() == 1) {
			cq.where(criteria.get(0));
		} else {
			cq.where(cb.and(criteria.toArray(new Predicate[0])));
		}

		
		Query query = em.createQuery(cq);
		
		if (searchCriteria.getSearchCriteria("serialNo")!= null) {
			Object obj = searchCriteria.getSearchCriteria("serialNo");
			if(obj instanceof InQueryHelper)
			{
				query.setParameter("serialNo", ((InQueryHelper)obj).getColumnValues());
			}
		}

		if(searchCriteria.getMaxRow()!=null)
			query.setMaxResults(searchCriteria.getMaxRow());

		return  query.getResultList();
	
	}
	
	public AssetEquipment findAssetEquipment(SearchCriteria searchCriteria) {
		if(searchCriteria==null) searchCriteria = new SearchCriteria();
		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery<AssetEquipment> cq = cb.createQuery(AssetEquipment.class);
		
		Root<AssetEquipment> root = cq.from(AssetEquipment.class);
		
		Join<AssetEquipment,AssetModel> assetModel = root.join("assetModel", JoinType.INNER);
		
		List<Predicate> criteria = new ArrayList<Predicate>();
		
		
		if (searchCriteria.getSearchCriteria("assetModelCode")!= null) {
			ParameterExpression<String> p = cb.parameter(String.class, "assetModelCode");
			criteria.add(cb.equal(assetModel.get("assetModelCode"),  p));
		}
		
		if (searchCriteria.getSearchCriteria("assetModelId")!= null) {
			ParameterExpression<Integer> p = cb.parameter(Integer.class, "assetModelId");
			criteria.add(cb.equal(assetModel.get("assetModelId"),  p));
		}
		
		if (searchCriteria.getSearchCriteria("serialNo")!= null) {
			ParameterExpression<String> p = cb.parameter(String.class, "serialNo");
			criteria.add(cb.equal(cb.upper(root.get("assetEquipmentPK").<String>get("serialNo")), cb.upper(p)));
		}
		
		cq.select(root);
		if (criteria.size() == 0) {
			} else if (criteria.size() == 1) {
			cq.where(criteria.get(0));
			} else {
			cq.where(cb.and(criteria.toArray(new Predicate[0])));
		}
		
		Query query = em.createQuery(cq);
		
		if (searchCriteria.getSearchCriteria("serialNo")!= null) {
			query.setParameter("serialNo", searchCriteria.getSearchCriteria("serialNo"));
		}
		
		if (searchCriteria.getSearchCriteria("assetModelCode")!= null) {
			query.setParameter("assetModelCode", searchCriteria.getSearchCriteria("assetModelCode"));
		}
		
		if (searchCriteria.getSearchCriteria("assetModelId")!= null) {
			query.setParameter("assetModelId", searchCriteria.getSearchCriteria("assetModelId"));
		}
		if(searchCriteria.getMaxRow()!=null)
			query.setMaxResults(searchCriteria.getMaxRow());
		
		List<AssetEquipment> list = query.getResultList();
		if(CollectionUtils.isNotEmpty(list)){
			return list.get(0);
		}
		
		return null;
	}
	
	public List<AssetEquipment> findAssetEquipments(SearchCriteria searchCriteria) {
		if(searchCriteria==null) searchCriteria = new SearchCriteria();
		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery<AssetEquipment> cq = cb.createQuery(AssetEquipment.class);
		
		Root<AssetEquipment> root = cq.from(AssetEquipment.class);
		
		Join<AssetEquipment,AssetModel> assetModel = root.join("assetModel", JoinType.INNER);
		
		Join<AssetEquipment,Location> location = root.join("location", JoinType.INNER);
		
		List<Predicate> criteria = new ArrayList<Predicate>();
		
		if (searchCriteria.getSearchCriteria("assetEquipmentStatus")!= null) {
			ParameterExpression<String> p = cb.parameter(String.class, "assetEquipmentStatus");
			criteria.add(cb.equal(root.get("assetEquipmentStatus"),  p));
		}
		
		if (searchCriteria.getSearchCriteria("assetModelCode")!= null) {
			ParameterExpression<String> p = cb.parameter(String.class, "assetModelCode");
			criteria.add(cb.equal(assetModel.get("assetModelCode"),  p));
		}
		
		if (searchCriteria.getSearchCriteria("assetModelId")!= null) {
			ParameterExpression<Integer> p = cb.parameter(Integer.class, "assetModelId");
			criteria.add(cb.equal(assetModel.get("assetModelId"),  p));
		}
		
		if (searchCriteria.getSearchCriteria("serialNo")!= null) {
			ParameterExpression<String> p = cb.parameter(String.class, "serialNo");
			criteria.add(cb.like(cb.upper(root.get("assetEquipmentPK").<String>get("serialNo")), cb.upper(p)));
		}
		
		if (searchCriteria.getSearchCriteria("locationCode")!= null) {
			
			Object obj = searchCriteria.getSearchCriteria("locationCode");
			
			if(obj instanceof InQueryHelper)
			{
				ParameterExpression<List> p = cb.parameter(List.class, "locationCode");
				criteria.add(cb.in(location.get("locationCode")).value(p));
			}
			else {
				ParameterExpression<String> p = cb.parameter(String.class, "locationCode");
				criteria.add(cb.equal(cb.upper(location.<String>get("locationCode")), cb.upper(p)));
			}
			
		}
		
		if (searchCriteria.getSearchCriteria("locationId")!= null) {
			ParameterExpression<Integer> p = cb.parameter(Integer.class, "locationId");
			criteria.add(cb.equal(location.<Integer>get("locationId"), p));
		}
		
		if (searchCriteria.getSearchCriteria("subLocationId")!= null) {
			ParameterExpression<String> p = cb.parameter(String.class, "subLocationId");
			criteria.add(cb.equal(cb.upper(root.<String>get("subLocationId")), cb.upper(p)));
		}
		
		if (searchCriteria.getSearchCriteria("companyCode")!= null) {
			ParameterExpression<String> p = cb.parameter(String.class, "companyCode");
			criteria.add(cb.equal(cb.upper(root.<String>get("companyCode")), cb.upper(p)));
		}
		
		cq.select(root);
		if (criteria.size() == 0) {
			} else if (criteria.size() == 1) {
			cq.where(criteria.get(0));
			} else {
			cq.where(cb.and(criteria.toArray(new Predicate[0])));
		}
		
		Query query = em.createQuery(cq);
		
		if (searchCriteria.getSearchCriteria("serialNo")!= null) {
			query.setParameter("serialNo", "%"+searchCriteria.getSearchCriteria("serialNo")+"%");
		}
		if (searchCriteria.getSearchCriteria("assetModelCode")!= null) {
			query.setParameter("assetModelCode", searchCriteria.getSearchCriteria("assetModelCode"));
		}
		if (searchCriteria.getSearchCriteria("assetEquipmentStatus")!= null) {
			query.setParameter("assetEquipmentStatus", searchCriteria.getSearchCriteria("assetEquipmentStatus"));
		}
		if (searchCriteria.getSearchCriteria("assetModelId")!= null) {
			query.setParameter("assetModelId", searchCriteria.getSearchCriteria("assetModelId"));
		}
		if (searchCriteria.getSearchCriteria("locationCode")!= null) {
			
			Object obj = searchCriteria.getSearchCriteria("locationCode");
			if (obj instanceof InQueryHelper) {
				query.setParameter("locationCode", ((InQueryHelper) obj).getColumnValues());
			}else{
				query.setParameter("locationCode", searchCriteria.getSearchCriteria("locationCode"));
			}
		}
		if (searchCriteria.getSearchCriteria("locationId")!= null) {
			query.setParameter("locationId", searchCriteria.getSearchCriteria("locationId"));
		}
		if (searchCriteria.getSearchCriteria("subLocationId")!= null) {
			query.setParameter("subLocationId", searchCriteria.getSearchCriteria("subLocationId"));
		}
		if (searchCriteria.getSearchCriteria("companyCode")!= null) {
			query.setParameter("companyCode", searchCriteria.getSearchCriteria("companyCode"));
		}
		
		if(searchCriteria.getMaxRow()!=null)
			query.setMaxResults(searchCriteria.getMaxRow());
		
		return query.getResultList();
	}
	
	public AssetComponent findAssetComponent(SearchCriteria searchCriteria) {
		searchCriteria.setMaxRow(1);
		List<AssetComponent> assetComponents= findAssetComponents(searchCriteria);
		if(assetComponents!=null && assetComponents.size()>=1)
			return assetComponents.get(0);
		
		return null;
	}
	
	public List<AssetComponent> findAssetComponents(SearchCriteria searchCriteria) {
		if(searchCriteria==null) searchCriteria = new SearchCriteria();
		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery<AssetComponent> cq = cb.createQuery(AssetComponent.class);
		
		Root<AssetComponent> root = cq.from(AssetComponent.class);
		
		Join<AssetComponent,AssetModel> assetModel = root.join("assetModel", JoinType.INNER);	
		
		Join<AssetComponent,Location> location = root.join("location", JoinType.INNER);
		
		List<Predicate> criteria = new ArrayList<Predicate>();
		
		if (searchCriteria.getSearchCriteria("assetModelCode")!= null) {
			ParameterExpression<String> p = cb.parameter(String.class, "assetModelCode");
			criteria.add(cb.equal(assetModel.get("assetModelCode"),  p));
		}
		
		if (searchCriteria.getSearchCriteria("assetModelId")!= null) {
			ParameterExpression<Integer> p = cb.parameter(Integer.class, "assetModelId");
			criteria.add(cb.equal(assetModel.get("assetModelId"),  p));
		}
		
		if (searchCriteria.getSearchCriteria("locationCode")!= null) {
			ParameterExpression<String> p = cb.parameter(String.class, "locationCode");
			criteria.add(cb.equal(cb.upper(location.<String>get("locationCode")), cb.upper(p)));
		}
		
		if (searchCriteria.getSearchCriteria("locationId")!= null) {
			ParameterExpression<Integer> p = cb.parameter(Integer.class, "locationId");
			criteria.add(cb.equal(location.<Integer>get("locationId"),p));
		}
		
		if (searchCriteria.getSearchCriteria("companyCode")!= null) {
			ParameterExpression<String> p = cb.parameter(String.class, "companyCode");
			criteria.add(cb.equal(root.get("companyCode"),  p));
		}
		
		cq.select(root);
		if (criteria.size() == 0) {
			} else if (criteria.size() == 1) {
			cq.where(criteria.get(0));
			} else {
			cq.where(cb.and(criteria.toArray(new Predicate[0])));
		}
		
		Query query = em.createQuery(cq);
		
		
		if (searchCriteria.getSearchCriteria("assetModelCode")!= null) {
			query.setParameter("assetModelCode", searchCriteria.getSearchCriteria("assetModelCode"));
		}
		if (searchCriteria.getSearchCriteria("assetModelId")!= null) {
			query.setParameter("assetModelId", searchCriteria.getSearchCriteria("assetModelId"));
		}
		if (searchCriteria.getSearchCriteria("locationCode")!= null) {
			query.setParameter("locationCode", searchCriteria.getSearchCriteria("locationCode"));
		}
		if (searchCriteria.getSearchCriteria("locationId")!= null) {
			query.setParameter("locationId", searchCriteria.getSearchCriteria("locationId"));
		}
		if (searchCriteria.getSearchCriteria("companyCode")!= null) {
			query.setParameter("companyCode", searchCriteria.getSearchCriteria("companyCode"));
		}

		if(searchCriteria.getMaxRow()!=null)
			query.setMaxResults(searchCriteria.getMaxRow());
		
		return query.getResultList();
	}
	
	public List<AssetBalance> findAssetBalance(SearchCriteria searchCriteria) {
		
		StringBuilder sb = new StringBuilder("from AssetBalance assetBalance " +
				"where assetBalancePK.assetBalanceMonth =:month and assetBalancePK.assetBalanceYear =:year and assetType =:type " +
				"order by assetBalancePK.assetBalanceYear desc, assetBalancePK.assetBalanceMonth desc");
		String assetBalanceMonth = (String)searchCriteria.getSearchCriteria("month");
		String assetBalanceYear = (String)searchCriteria.getSearchCriteria("year");
		String assetBalanceType = (String)searchCriteria.getSearchCriteria("type");
		Query query = em.createQuery(sb.toString());
		query.setParameter("month", assetBalanceMonth);
		query.setParameter("year", assetBalanceYear);
		query.setParameter("type", assetBalanceType);
		return query.getResultList();
		
	}
	
	public AssetModel findAssetModel(SearchCriteria searchCriteria) {
		searchCriteria.setMaxRow(1);
		List<AssetModel> assetModels = findAssetModels(searchCriteria);
		if(assetModels!=null && assetModels.size()>=1)
			return assetModels.get(0);
		
		return null;
	}

	public SearchResult searchAssetModel(
			SearchCriteria searchCriteria) {
		
		if(searchCriteria.getCurPage()==null)
			searchCriteria.setCurPage(1);
		
		AssetModelPaginationHelper helper = new AssetModelPaginationHelper();
		
		return helper.getSearchResultFromQueryHelper(em, searchCriteria);
	}
	
	public SearchResult searchAssetEquipment(
			SearchCriteria searchCriteria) {
		if(searchCriteria.getCurPage()==null)
			searchCriteria.setCurPage(1);
		
		AssetEquipmentPaginationHelper helper = new AssetEquipmentPaginationHelper();
		
		return helper.getSearchResultFromQueryHelper(em, searchCriteria);
	}
	
	public SearchResult searchAssetComponentStock(
			SearchCriteria searchCriteria) {
		if(searchCriteria.getCurPage()==null)
			searchCriteria.setCurPage(1);
		
		AssetCompStockPaginationHelper helper = new AssetCompStockPaginationHelper();
		
		return helper.getSearchResultFromQueryHelper(em, searchCriteria);
	}
	
	
	
	
	public List<AssetBalance> searchAssetBalance(SearchCriteria searchCriteria) {
	
		String assetBalanceMonth = (String)searchCriteria.getSearchCriteria("assetBalanceMonth");
		String assetBalanceYear = (String)searchCriteria.getSearchCriteria("assetBalanceYear");
		
		
		Query query = em.createQuery("from AssetBalance");
		
		if(StringUtils.isNotEmpty(assetBalanceMonth)){
			query.setParameter("month", "%" + assetBalanceMonth + "%");
		}
		
		if(StringUtils.isNotEmpty(assetBalanceMonth)){
			query.setParameter("year", "%" +assetBalanceYear+ "%" );
		}
		
		return  query.getResultList();
	}
	
	public void deleteAssetModel(Integer assetModelId) {
		
		Query query = em.createQuery("update AssetModel set active=0 where assetModelId=:assetModelId");
		
		query.setParameter("assetModelId", assetModelId);
		
		query.executeUpdate();
		
	}
	
	public void deleteAssetModel(AssetModel assetModel) {
		
		assetModel.setDateModified(new Date());
		assetModel.setActive(false);
		
		em.merge(assetModel);
	}
	
	public static class AssetModelPaginationHelper extends PaginationHelper<AssetModel> {

		@Override
		public String[] getValidColumnName() {
			return new String[]{"assetModelCode","assetModelName","active","assetType","accessDepartmentId" };
		}
		
		@Override
		public String[] getValidSortName() {
			return new String[]{"assetModelCode","assetModelName","assetType","modifiedBy","dateModified"};
		}

		@Override
		public String getDefaultSortName() {
			return "assetModelCode";
		}
		
		@Override
		public QueryHelper getQueryHelper() {
			QueryHelper queryHelper = new QueryHelper();
			queryHelper.select("select assetModel from AssetModel assetModel");
			return queryHelper;
		}
	}
	
	public static class AssetEquipmentPaginationHelper extends PaginationHelper<AssetEquipment> {

		@Override
		public String[] getValidColumnName() {
			return new String[]{"assetModel.assetModelName","assetEquipmentStatus","subLocationId","assetEquipmentPK.serialNo","location.locationName","companyCode","assetModel.active"};
		}
		
		@Override
		public String[] getValidSortName() {
			return new String[]{"assetModel.assetModelName","assetEquipmentStatus","subLocationId","assetEquipmentPK.serialNo","location.locationName","unitPrice"};
		}

		@Override
		public String getDefaultSortName() {
			return "assetEquipmentPK.serialNo";
		}
		
		@Override
		public QueryHelper getQueryHelper() {
			QueryHelper queryHelper = new QueryHelper();
			queryHelper.select("select assetEquipment from AssetEquipment assetEquipment");
			return queryHelper;
		}
	}
	
	
	public static class AssetCompStockPaginationHelper extends PaginationHelper<AssetComponent> {

		@Override
		public String[] getValidColumnName() {
			return new String[]{"assetModel.assetModelName","location.locationName","companyCode"};
		}
		
		@Override
		public String[] getValidSortName() {
			return new String[]{"assetModel.assetModelName","location.locationName","companyCode","quantity"};
		}

		@Override
		public String getDefaultSortName() {
			return "assetModel.assetModelName";
		}
		
		@Override
		public QueryHelper getQueryHelper() {
			QueryHelper queryHelper = new QueryHelper();
			queryHelper.select("select assetComponent from AssetComponent assetComponent");
			return queryHelper;
		}
	}
	
	public static class AssetBalancePaginationHelper extends PaginationHelper<AssetBalance> {

		@Override
		public String[] getValidColumnName() {
			return new String[]{"assetBalancePK.assetBalanceMonth","assetBalancePK.assetBalanceYear","createdBy","dateCreated"};
		}
		
		@Override
		public String[] getValidSortName() {
			return new String[]{"assetBalancePK.assetBalanceMonth","assetBalancePK.assetBalanceYear","createdBy","dateCreated"};
		}

		@Override
		public String getDefaultSortName() {
			return "dateCreated desc";
		}
		
		@Override
		public QueryHelper getQueryHelper() {
			QueryHelper queryHelper = new QueryHelper();
			queryHelper.select("select assetBalance from AssetBalance assetBalance");
			return queryHelper;
		}
	}
	
	private Date getStartDate(String month, String year){
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM"); 
	    String datetime= year + "-" + month; 
	    Date start = null;
	    try {
	    	start =  sdf.parse(datetime);
		} catch (ParseException e) {
			logger.error(CommonUtil.getStackTrace(e));
		} 
		return start;
	}
	
	private Date getEndDate(Date start){
	    Date end = null;
	    if(start != null){
			Calendar t = Calendar.getInstance(); 
			t.setTime(start);
			t.add(Calendar.MONTH, 1); 
			end = t.getTime();
	    }
	    return end;
	}
	
	private List<String> validate(Date start, Date end){
		Query purchaseOrderQuery = em.createQuery("from PurchaseOrder purchaseOrder where dateCreated >= :start and dateCreated < :end and purchaseOrderStatus in (:status)");
		purchaseOrderQuery.setParameter("start", start);
		purchaseOrderQuery.setParameter("end", end);
		List<String> status = Arrays.asList(new String[]{PurchaseOrder.PURCHASEORDERTYPE_NEW, PurchaseOrder.PURCHASEORDERSTATUS_INPROCESS});
		purchaseOrderQuery.setParameter("status", status);	
		List<PurchaseOrder> purchaseOrderList = purchaseOrderQuery.getResultList();		
		
		Query assetTransactionQuery = em.createQuery("from AssetTransaction assetTransaction where dateCreated >= :start and dateCreated < :end and assetTransactionType in (:types) and assetTransactionStatus = :status");
		assetTransactionQuery.setParameter("start", start);
		assetTransactionQuery.setParameter("end", end);
		assetTransactionQuery.setParameter("status", AssetTransaction.TRANSACTIONSTATUS_PENDING);
		List<String> types = Arrays.asList(new String[]{AssetTransaction.TRANSACTIONTYPE_ISSUEORDER,  AssetTransaction.TRANSACTIONTYPE_EQUIPMENTTRANSFER, AssetTransaction.TRANSACTIONTYPE_FAULTYTRANSFER });
		assetTransactionQuery.setParameter("types", types);
		List<AssetTransaction> assetTransactionList = assetTransactionQuery.getResultList();
		
		List<String> message = new ArrayList<String>();
		if(CollectionUtils.isNotEmpty(purchaseOrderList) || CollectionUtils.isNotEmpty(assetTransactionList)){
			for(PurchaseOrder po : purchaseOrderList){
				message.add("Purchase Order " + po.getPurchaseOrderCode());
			}
		
			for(AssetTransaction at : assetTransactionList){
				message.add("Issue Order " + at.getAssetTransactionCode());
			}
		}
		return message;
	}
	
	private void deleteCurrentAssetBalance(String month, String year){
		//delete first
		Query deleteAssetBalance = em.createQuery("from AssetBalance assetBalance where assetBalanceMonth = :month and assetBalanceYear = :year");
		deleteAssetBalance.setParameter("month", month);
		deleteAssetBalance.setParameter("year", year);
		List<AssetBalance> resultList = deleteAssetBalance.getResultList();

		if(CollectionUtils.isNotEmpty(resultList)){
			AssetBalance result = resultList.get(0);
			//delete items
			Query deleteAssetBalanceItem = em.createNativeQuery("delete from AMTB_ASSET_BALANCE_ITEM where ASSETBALANCEID = :blanceId");
			deleteAssetBalanceItem.setParameter("blanceId", result.getAssetBalanceId());
			deleteAssetBalanceItem.executeUpdate();
			//delete AssetBalance
			em.remove(result);
		}
	}
	
	private void insertCurrentAssetBalance(Date createDate, String month, String year, String addUser){
		 	deleteCurrentAssetBalance(month, year);
			
			AssetBalance assetBalance = new AssetBalance();
			assetBalance.setAssetBalanceMonth(month);
			assetBalance.setAssetBalanceYear(year);
			assetBalance.setDateCreated(new Date());
			assetBalance.setDateModified(new Date());
			assetBalance.setCreatedBy(addUser);
			em.persist(assetBalance);

			//store assetEquipment
			Query queryAssetEquments = em.createQuery("select new AssetBalanceItem(count(assetEquipmentPK.serialNo) as total, unitPrice, assetEquipmentPK.assetModel.assetModelId as assetModelId, location.locationId, companyCode, '"+AssetModel.ASSETTYPE_EQUIPMENT+"')" +
					" from AssetEquipment equipment where assetEquipmentStatus in (:status) " +
					" group by assetEquipmentPK.assetModel.assetModelId, unitPrice, companyCode, location.locationId ");

			List<String> status = Arrays.asList(new String[]{AssetModel.ASSETSTATUS_ACTIVE, AssetModel.ASSETSTATUS_FAULT, AssetModel.ASSETSTATUS_REPAIR});
			queryAssetEquments.setParameter("status", status);
			
			List<AssetBalanceItem> list = queryAssetEquments.getResultList();
			for(AssetBalanceItem tmp : list){
				tmp.setAssetBalance(assetBalance);
				tmp.setDateCreated(new Date());
				tmp.setCreatedBy(addUser);
				em.merge(tmp);
			}
			
			//store assetComponent
			Query queryBlanceView = em.createQuery("from BlanceView blanceView");
			List<BlanceView> listBlance = queryBlanceView.getResultList();
			for(BlanceView view : listBlance){
				AssetBalanceItem item = new AssetBalanceItem();
				valueCopy(view, item);
				item.setAssetBalance(assetBalance);
				item.setDateCreated(new Date());
				item.setCreatedBy(addUser);
				em.merge(item);
			}
	}
	
	private void valueCopy(BlanceView view, AssetBalanceItem item){
		AssetModel assetModel = new AssetModel();
		Location location = new Location();
		assetModel.setAssetModelId(view.getAssetModelId());
		location.setLocationId(view.getLocationId());
		item.setAssetModel(assetModel);
		item.setAssetType(AssetModel.ASSETTYPE_COMPONENT);
		item.setCompanyCode(view.getCompanyCode());
		item.setLocation(location);
		item.setQuantityOnHand(view.getBlance());
		item.setUnitPrice(view.getUnitPrice());
		item.setVendorId(view.getVendorId());
		
	}
	
	public List<String> createAssetBalance(String month, String year, String addUser) {
	    Date start = getStartDate(month, year);
	    Date end = getEndDate(start);

	    List<String> message = validate(start, end);
	    if(CollectionUtils.isNotEmpty(message)){
	    	return message;
	    }
	    
	    insertCurrentAssetBalance(start, month, year, addUser);
		return null;
	}
	
	public void createAssetSchedulerBalance(String month, String year, String addUser) {
	    Date start = getStartDate(month, year);
	    //no need validate
	    insertCurrentAssetBalance(start, month, year, addUser);
	}
	
	

	public void createAssetEquipment(AssetEquipment assetEquipment) {
		
		AssetEquipment newAssetEquipment = new AssetEquipment();
		
		assetEquipment.setModifiedBy(assetEquipment.getCreatedBy());
		assetEquipment.setDateCreated(new Date());
		assetEquipment.setDateModified(new Date());
		
		BeanUtils.copyProperties(assetEquipment,newAssetEquipment);
		
		em.merge(newAssetEquipment);
	}

	public void createAssetComponent(AssetComponent assetComponent) {
		
		AssetComponent newAssetComponent = new AssetComponent();
		
		assetComponent.setModifiedBy(assetComponent.getCreatedBy());
		assetComponent.setDateCreated(new Date());
		assetComponent.setDateModified(new Date());
		
		BeanUtils.copyProperties(assetComponent,newAssetComponent);
		
		em.merge(newAssetComponent);
		
	}

	public boolean isAllowedToDelete(AssetModel assetModel) {
		
		if(assetModel.isEquipment())
		{
		SearchCriteria searchCriteria = new SearchCriteria();
		searchCriteria.addSearchCriteria("assetModelCode", assetModel.getAssetModelCode());
		searchCriteria.setMaxRow(1);
		AssetEquipment assetEquipment = findAssetEquipment(searchCriteria);
		return assetEquipment==null;
		
		}
		else
		{
			SearchCriteria searchCriteria = new SearchCriteria();
			searchCriteria.addSearchCriteria("assetModelCode", assetModel.getAssetModelCode());
			searchCriteria.setMaxRow(1);
			AssetComponent assetComponent = findAssetComponent(searchCriteria);
			return assetComponent==null;
		}
		
		
	}

}
